﻿/*********************************************************
 * 开发人员：小兵
 * 创建时间：2012-10-9 15:15:51
 * 描述说明：
 * 
 * 更改历史：
 * 
 * *******************************************************/
namespace Mozlite.Data.SqlCE
{
    using System;
    using System.Web;
    using System.Data;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlServerCe;

    /// <summary>
    /// MSSQL数据库辅助类。
    /// </summary>
    public class SqlCeHelper : SqlHelper<System.Data.SqlServerCe.SqlCeParameter>
    {
        /// <summary>
        /// 初始化类<see cref="SqlCeHelper"/>。
        /// </summary>
        /// <param name="connectionString">链接字符串。</param>
        public SqlCeHelper(string connectionString)
            : base(connectionString)
        {
            installer = new SqlCeFactory(this);
        }

        /// <summary>
        /// 创建参数对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="parameterName">参数名称。</param>
        /// <param name="value">参数值。</param>
        /// <returns>返回参数对象实例。</returns>
        public override IParameter CreateParameter(string parameterName, object value)
        {
            return new SqlCeParameter(parameterName, value ?? DBNull.Value);
        }

        /// <summary>
        /// 启动事务处理对象实例。
        /// </summary>
        /// <return>当前事务处理实例对象。</return>
        public override IDbTransaction BeginTransaction()
        {
            var connection = new SqlCeConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();
            return connection.BeginTransaction();
        }

        /// <summary>
        /// 执行单值返回方法。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回执行后的结果。</returns>
        protected override object ExecuteScalar(IDbTransaction transaction, CommandType commandType, string commandText, System.Data.SqlServerCe.SqlCeParameter[] parameters)
        {
            var connection = GetConnection(transaction);
            var command = new SqlCeCommand();
            PrepareCommand(command, connection, null, commandType, commandText, parameters);
            var value = command.ExecuteScalar();
            command.Parameters.Clear();
            if(transaction == null)
                connection.Close();
            return value;
        }

        /// <summary>
        /// 执行不返回值得查询语句。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回影响的行数。
        /// </returns>
        protected override int ExecuteNonQuery(IDbTransaction transaction, CommandType commandType, string commandText, System.Data.SqlServerCe.SqlCeParameter[] parameters)
        {
            if (commandText.StartsWith(SqlCeParser.SyntaxSQLCEMarkSave))
            {
                var commands = commandText.Split(new string[] { SqlCeParser.SyntaxSQLCESep }, StringSplitOptions.RemoveEmptyEntries);
                if (commands.Length == 3)
                {
                    var value = ExecuteScalar(transaction, commandType, commands[0].Substring(SqlCeParser.SyntaxSQLCEMarkSave.Length), parameters);
                    if (value == null)
                        commandText = commands[2];
                    else
                        commandText = commands[1];
                }
                else
                    return 0;
            }
            return ExecuteNonQuerySyntaxFilter(transaction, commandType, commandText, parameters);
        }

        private int ExecuteNonQuerySyntaxFilter(IDbTransaction transaction, CommandType commandType, string commandText, System.Data.SqlServerCe.SqlCeParameter[] parameters)
        {
            var connection = GetConnection(transaction);
            var command = new SqlCeCommand();
            PrepareCommand(command, connection, null, commandType, commandText, parameters);
            int rowAffected = command.ExecuteNonQuery();
            command.Parameters.Clear();
            if (transaction == null)
                connection.Close();
            return rowAffected;
        }

        /// <summary>
        /// 执行查询语句。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>
        /// 返回数据库读取对象实例。
        /// </returns>
        protected override IDataReader ExecuteReader(IDbTransaction transaction, CommandType commandType, string commandText, System.Data.SqlServerCe.SqlCeParameter[] parameters)
        {
            var connection = GetConnection(transaction);
            var command = new SqlCeCommand();
            try
            {
                PrepareCommand(command, connection, null, commandType, commandText, parameters);
                IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch
            {
                if(transaction == null)
                    connection.Close();
                throw;
            }
        }

        private SqlCeParser sqlParser = new SqlCeParser();

        /// <summary>
        /// 转换SQL语句。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <returns>默认返回当前SQL语句，子类进行重新转换。</returns>
        protected override string ConvertCommand(string commandText)
        {
            return sqlParser.Parse(commandText);
        }

        private SqlCeConnection GetConnection(IDbTransaction transaction)
        {
            if(transaction == null)
                return new SqlCeConnection(ConnectionString);
            return (SqlCeConnection)transaction.Connection;
        }

        private void PrepareCommand(SqlCeCommand cmd, SqlCeConnection conn, IDbTransaction trans, CommandType cmdType, string cmdText, System.Data.SqlServerCe.SqlCeParameter[] cmdParms)
        {
            if(conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if(trans != null)
                cmd.Transaction = (SqlCeTransaction)trans;

            cmd.CommandType = cmdType;

            if(cmdParms != null)
            {
                foreach(var parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 返回分页SQL语句字符串。
        /// </summary>
        /// <param name="pageIndex">页码，从1开始。</param>
        /// <param name="pageSize">每页显示记录数。</param>
        /// <param name="fields">选择列。</param>
        /// <param name="tbWheres">FROM语句后ORDER BY之前的语句。</param>
        /// <param name="orderby">ORDER BY语句。</param>
        /// <returns>返回组合后的分页查询语句。</returns>
        protected override string ApplyPageCommandText(int pageIndex, int pageSize, string fields, string tbWheres, string orderby)
        {
            return "SELECT {1} FROM {2}{0} OFFSET {3} ROWS FETCH NEXT {4} ROWS ONLY;\r\nSELECT COUNT(1) FROM {2};".FormatWith(
                orderby, fields, tbWheres, (pageIndex - 1) * pageSize, pageSize);
        }

        /// <summary>
        /// 分页查询，并返回总数量。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="size">返回数据总记录数。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回数据列表。</returns>
        protected override List<T> ExecuteReader<T>(string commandText, IParameter[] parameters, out int size)
        {
            var sqls = commandText.Split(new string[] { ";\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            size = ExecuteScalar<int>(sqls[1], parameters);
            var list = new List<T>();
            using (var reader = ExecuteReader(sqls[0], parameters))
            {
                while (reader.Read())
                {
                    list.Add(reader.As<T>());
                }
            }
            return list;
        }
    }
}
